"""
数据库增删改查
"""
from pymysql import Connection

conn = Connection(
    host='localhost',
    port=3306,
    user='root',
    passwd='root',
    database='db04',
    autocommit=True,  # 配置上这个参数后，不需要手动提交
)
# 创建游标对象
cursor = conn.cursor()

def update_stu(id,name=None,age=None,gender=None,address=None):
    """
    功能： 根据id修改学生信息
    :param name:  要修改的姓名
    :param age: 要修改的年龄
    :param gender:  要修改的性别
    :param address:  要修改的地址
    :return:  返回受影响的行数
    """
    # 准备一个列表用于存放参数
    params = []  #用于拼接SQL字符串的
    updates = [] # 用于存修改的数据的
    # 判断参数是否为空
    if name is not None:
        params.append('name=%s')
        updates.append(name)
    if age is not None:
        params.append('age=%s')
        updates.append(age)
    if gender is not None:
        params.append('gender=%s')
        updates.append(gender)
    if address is not None:
        params.append('address=%s')
        updates.append(address)

    if params:
        sql = 'update student set ' + ','.join(params) +' where id= %s'
        # 将id传入
        updates.append(id)
        cursor.execute(sql,updates)
        conn.commit()

    cursor.close()
    conn.close()
    return cursor.rowcount # 返回受影响的行数

def get_stu_likename(name):
    """
    功能：根据姓名模糊查询
    :param name:   传入姓名
    :return:  返回符合条件的学生信息
    """
    sql = 'select * from student where name like %s'
    cursor.execute(sql, f'%{name}%')
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result

def get_stu_byId(id):
    """
    功能：根据id查询学生信息
    :param id:  要查询的学生id
    :return:  返回要查询的学生信息
    """
    sql = 'select * from student where id = %s'
    cursor.execute(sql,id)

    stu = cursor.fetchall()

    cursor.close()
    conn.close()

    return stu


def select_all_student():
    """
    功能：查询所有学生信息
    :return: 学生元祖
    """
    sql = "select * from student"
    # 执行SQL
    cursor.execute(sql)
    # 获取执行结果
    result = cursor.fetchall()

    cursor.close()
    conn.close()
    return result

def del_student(id):
    """
    功能：根据id删除学生信息
    :param id:  要删除的学生的id
    """

    # 定制SQL
    sql = 'delete from student where id=%s'
    # 执行SQL
    result = cursor.execute(sql,id)
    print(result)

    cursor.close()
    conn.close()



def add_student(name,age,gender,address):
    """
    功能:添加学生信息
    :return: 返回自增的id
    """
    # 定义sql
    sql = f'insert into student(name,age,gender,address) values(%s,%s,%s,%s)'

    # 执行SQL
    cursor.execute(sql,(name,age,gender,address))

    # 手动提交  增删 改数据都需要手动提交
    conn.commit()

    # 释放资源
    cursor.close()
    conn.close()

    return cursor.lastrowid  # 这里返回的是自增的id

if __name__ == '__main__':
    # id = add_student('韩耀祖',19,'男','河北省邯郸曲')
    # if id:
    #     print('数据添加成功')
    # else:
    #     print('数据添加失败')

    # del_student(3)
    #
    # result = select_all_student()
    # print(result)

    # stu = get_stu_byId(2)
    # print(stu)

    # result = get_stu_likename('韩')
    # print(result)

    row = update_stu(2,'谭新宇',gender='男')
    if row:
        print(row,'行数据修改成功')
    else:
        print('修改失败')